package com.kdzy.lshy.socity_server.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.kdzy.lshy.socity_server.common.Constants;
import com.kdzy.lshy.socity_server.common.ResultMap;
import com.kdzy.lshy.socity_server.repository.BaseDao;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import redis.clients.jedis.Jedis;

import javax.servlet.http.HttpServletRequest;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 我的预约
 *  1.居民预约查询
 *  2.居民预约详情
 *  3.办事员不接受预约调整（增，改，查）
 *  4.办事员不接受预约时间记录删除（逻辑删除）
 *
 * @author sjj
 */

@Service
public class ReservationService extends BaseService {

    @Autowired
    private BaseDao baseDao;

    private static final Logger logger = LoggerFactory.getLogger(ReservationService.class);

    //居民预约查询
    public Object demandMyReservation(String param, HttpServletRequest request) {

        List list = new ArrayList();
        String status = null;
        String info = null;
        Object content = null;


        //获取解析JSON字符串
        JSONObject jsonObject = JSON.parseObject(param);

        //**********************************************************
        if (null == jsonObject) {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status, info, content);
        }

        LinkedHashMap<Object, Object> map = null;

        String systoken = null;
        if (!(jsonObject.getInteger("visit_plat") != 0)) { // 网页端
            systoken = jsonObject.getString("systoken"); // 全局token
            if (StringUtils.isBlank(systoken)) {
                logger.info("systoken为空");
                map = ResultMap.creatReault("33", "失败", "token为空");
                logger.info("出去《居民预约查询》demandMyReservation()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }
        }

        String dwellerIdcard = null;
        String dwellerId = null;
        if (jsonObject.getInteger("visit_plat") == 0) { // 网页端

            // 根据身份证从redis中获取token
            Jedis jedis = new Jedis("140.143.210.101", 6379);
            jedis.auth("q1w2e3r4t5");

            List<String> infoList = jedis.hmget(systoken, "systoken",  "dwellerIdcard", "dwellerId");
            String systokenRedis = null;
            if (infoList != null && infoList.size() > 0) {
                systokenRedis = infoList.get(0); // token
                dwellerIdcard = infoList.get(1); // 居民身份证号
                dwellerId = infoList.get(2); // 居民唯一标识
            }

            // 查看token是否过期
            if (StringUtils.isBlank(systokenRedis)) { // 重新登录，弹出登录框
                logger.info("全局systoken过期，需要重新获取systoken");
                map = ResultMap.creatReault("33", "失败", "systoken过期");
                logger.info("出去《居民预约查询》demandMyReservation()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

            // token不一致
            if (!StringUtils.equals(systokenRedis, systoken)) {
                logger.info("用户访问的token不一致");
                map = ResultMap.creatReault("331", "失败", "systoken不一致");
                logger.info("出去《居民预约查询》demandMyReservation()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

        }
        //**********************************************************

        //StringBuffer动态写sql语句
        StringBuffer sql = new StringBuffer("SELECT b.ID,d.dweller_name,b.time, bz.bizz_name,b.bizz_type,b.create_time " +
                "FROM bespeak b,dweller d,bizz bz " +
                "WHERE b.dweller_id = d.ID AND b.bizz_id = bz.ID");
        List<Object> tags = new ArrayList<Object>();
        //解析居民ID，拼接居民ID的sql
        if (StringUtils.isNoneBlank(jsonObject.getString("dweller_id"))) {
            sql.append(" AND b.dweller_id = ?");
            tags.add(jsonObject.getString("dweller_id"));
        } else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status,info,content);
        }
        //解析数据版本
        if (StringUtils.isNoneBlank(jsonObject.getString("version"))){
            sql.append(" AND b.version = ?");
            tags.add(Integer.parseInt(jsonObject.getString("version")));
        }
        //解析访问类型
        if (StringUtils.isNoneBlank(jsonObject.getString("visit_type"))){
            sql.append(" AND b.visit_type = ?");
            tags.add(Integer.parseInt(jsonObject.getString("visit_type")));
        }
        //解析数据的有效性
        if (StringUtils.isNoneBlank(jsonObject.getString("is_used"))){
            sql.append(" AND b.is_used = ?");
            tags.add(Integer.parseInt(jsonObject.getString("is_used")));
        }
        //条件搜索业务名
        if (StringUtils.isNoneBlank(jsonObject.getString("bizz_name"))) {
            sql.append(" AND bz.bizz_name LIKE ?");
            tags.add("%" + jsonObject.getString("bizz_name") + "%");
        }
        //条件搜索业务状态
        if (StringUtils.isNoneBlank(jsonObject.getString("bizz_type"))) {
            sql.append(" AND b.bizz_type = ?");
            tags.add(Integer.parseInt(jsonObject.getString("bizz_type")));
        }
        sql.append(" order by b.time");
        //分页
        Integer page = Integer.valueOf(request.getParameter("offset"));//页数
        Integer rows = Integer.valueOf(request.getParameter("limit"));//每页行数

        long total = 0;
        String countsql = "select count(1) as count from (" + sql.toString() + ") as t";
        try {
            total = baseDao.getCount(countsql,tags);
        } catch (Exception e) {
            logger.error(e.getMessage());
        }

        String sql1 = sql.toString() + " limit " + page + "," + rows + "";


        try {
            list = baseDao.queryForList(sql1, tags.toArray());
            status = Constants.http_status_success;
            info = Constants.http_info_success;
            content = list;
        } catch (Exception e) {
            logger.error(e.getMessage());
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "后台处理异常";
        }

        map = ResultMap.creatReault(status, info, content);
        map.put("total", total);

        return map;
    }


    //居民预约详情
    public Object myReservationDetails(String param, HttpServletRequest request) {

        List list = new ArrayList();
        String status = null;
        String info = null;
        Object content = null;

        //获取解析JSON字符串
        JSONObject jsonObject = JSON.parseObject(param);

        //**********************************************************
        if (null == jsonObject) {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status, info, content);
        }

        LinkedHashMap<Object, Object> map = null;

        String systoken = null;
        if (!(jsonObject.getInteger("visit_plat") != 0)) { // 网页端
            systoken = jsonObject.getString("systoken"); // 全局token
            if (StringUtils.isBlank(systoken)) {
                logger.info("systoken为空");
                map = ResultMap.creatReault("33", "失败", "token为空");
                logger.info("出去《居民预约详情》myReservationDetails()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }
        }

        String dwellerIdcard = null;
        String dwellerId = null;
        if (jsonObject.getInteger("visit_plat") == 0) { // 网页端

            // 根据身份证从redis中获取token
            Jedis jedis = new Jedis("140.143.210.101", 6379);
            jedis.auth("q1w2e3r4t5");

            List<String> infoList = jedis.hmget(systoken, "systoken",  "dwellerIdcard", "dwellerId");
            String systokenRedis = null;
            if (infoList != null && infoList.size() > 0) {
                systokenRedis = infoList.get(0); // token
                dwellerIdcard = infoList.get(1); // 居民身份证号
                dwellerId = infoList.get(2); // 居民唯一标识
            }

            // 查看token是否过期
            if (StringUtils.isBlank(systokenRedis)) { // 重新登录，弹出登录框
                logger.info("全局systoken过期，需要重新获取systoken");
                map = ResultMap.creatReault("33", "失败", "systoken过期");
                logger.info("出去《居民预约详情》myReservationDetails()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

            // token不一致
            if (!StringUtils.equals(systokenRedis, systoken)) {
                logger.info("用户访问的token不一致");
                map = ResultMap.creatReault("331", "失败", "systoken不一致");
                logger.info("出去《居民预约详情》myReservationDetails()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

        }
        //**********************************************************

        //StringBuffer动态写sql语句
        StringBuffer sql = new StringBuffer("SELECT b.ID,d.dweller_name,b.time,bz.bizz_name,b.bizz_type,b.bizz_fall_res,b.bizz_succ_note,b.update_time,d.dweller_idcard,d.telphone,b.create_time " +
                "FROM bespeak b,dweller d,bizz bz " +
                "WHERE b.dweller_id = d.ID AND b.bizz_id = bz.ID");
        List<Object> tags = new ArrayList<Object>();
        //解析记录ID
        if (StringUtils.isNoneBlank(jsonObject.getString("id"))) {
            sql.append(" AND b.ID = ?");
            tags.add(jsonObject.getString("id"));
        }
        else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status,info,content);
        }
        //解析数据版本
        if (StringUtils.isNoneBlank(jsonObject.getString("version"))){
            sql.append(" AND b.version = ?");
            tags.add(Integer.parseInt(jsonObject.getString("version")));
        }
        //解析访问类型
        if (StringUtils.isNoneBlank(jsonObject.getString("visit_type"))){
            sql.append(" AND b.visit_type = ?");
            tags.add(Integer.parseInt(jsonObject.getString("visit_type")));
        }
        //解析数据有效性
        if (StringUtils.isNoneBlank(jsonObject.getString("is_used"))){
            sql.append(" AND b.is_used = ?");
            tags.add(Integer.parseInt(jsonObject.getString("is_used")));
        }


        try {
            content = baseDao.queryForList(sql.toString(), tags.toArray());
            status = Constants.http_status_success;
            info = Constants.http_info_success;
        } catch (Exception e) {
            logger.error(e.getMessage());
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "后台处理异常";
        }
        return ResultMap.creatReault(status, info, content);
    }


    //办事员不接受预约时间调整
    public Object judge(String param, HttpServletRequest request) {

        //获取解析JSON字符串
        JSONObject jsonObject = JSON.parseObject(param);

        //**********************************************************
        if (null == jsonObject) {
            return ResultMap.creatReault(Constants.http_status_fail, Constants.http_info_fali, "参数为空");
        }

        LinkedHashMap<Object, Object> map = null;

        String systoken = null;
        if (!(jsonObject.getInteger("visit_plat") != 0)) { // 网页端
            systoken = jsonObject.getString("systoken"); // 全局token
            if (StringUtils.isBlank(systoken)) {
                logger.info("systoken为空");
                map = ResultMap.creatReault("33", "失败", "token为空");
                logger.info("出去《办事员不接受预约时间调整》dpmtReservationAdjust()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }
        }

        String dwellerIdcard = null;
        String dwellerId = null;
        if (jsonObject.getInteger("visit_plat") == 0) { // 网页端

            // 根据身份证从redis中获取token
            Jedis jedis = new Jedis("140.143.210.101", 6379);
            jedis.auth("q1w2e3r4t5");

            List<String> infoList = jedis.hmget(systoken, "systoken",  "dwellerIdcard", "dwellerId");
            String systokenRedis = null;
            if (infoList != null && infoList.size() > 0) {
                systokenRedis = infoList.get(0); // token
                dwellerIdcard = infoList.get(1); // 居民身份证号
                dwellerId = infoList.get(2); // 居民唯一标识
            }

            // 查看token是否过期
            if (StringUtils.isBlank(systokenRedis)) { // 重新登录，弹出登录框
                logger.info("全局systoken过期，需要重新获取systoken");
                map = ResultMap.creatReault("33", "失败", "systoken过期");
                logger.info("出去《办事员不接受预约时间调整》dpmtReservationAdjust()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

            // token不一致
            if (!StringUtils.equals(systokenRedis, systoken)) {
                logger.info("用户访问的token不一致");
                map = ResultMap.creatReault("331", "失败", "systoken不一致");
                logger.info("出去《办事员不接受预约时间调整》dpmtReservationAdjust()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

        }
        //**********************************************************

           /*
            *工作方式
            *   1：查询
            *   2：修改
            *   3：添加
            * */
            if ("1".equals(jsonObject.getString("working"))) {
                return getAllByCondition(param, request);
            } else if ("2".equals(jsonObject.getString("working"))) {
                return update(param);
            } else if ("3".equals(jsonObject.getString("working"))) {
                return insert(param);
            }

        return null;
    }

    //插入
    @Override
    public Object insert(String param) {
        String status = null;
        String info = null;
        Object content = null;
        //获取解析JSON字符串
        JSONObject jsonObject = JSON.parseObject(param);

        //获取随机字符串作为主键ID
        String id1 = UUID.randomUUID().toString().replace("-", "");
        String id2 = UUID.randomUUID().toString().replace("-", "");

        //StringBuffer动态写cancel_bespeak表sql插入语句
        StringBuffer sql1 = new StringBuffer("INSERT INTO cancel_bespeak (");
        List<Object> tags1 = new ArrayList<Object>();
        //添加主键ID
        if (id1 != null && !"".equals(id1)) {
            sql1.append("ID,");
            tags1.add(id1);
        }
        //解析添加办事员ID
        if (StringUtils.isNoneBlank(jsonObject.getString("officer_id"))){
            sql1.append("officer_id,");
            tags1.add(jsonObject.getString("officer_id"));
        }
        //解析添加不接受预约类型（0 临时  1 周期）
        if (StringUtils.isNoneBlank(jsonObject.getString("cancel_type"))) {
            sql1.append("cancel_type,");
            tags1.add(Integer.parseInt(jsonObject.getString("cancel_type")));
        }
        //解析添加时间戳及时间戳转换的创建时间
        if (StringUtils.isNoneBlank(jsonObject.getLong("stamp") + "")) {
            sql1.append("create_time,");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String sd = sdf.format(new Date(Long.parseLong(jsonObject.getLong("stamp") + "")));
            tags1.add(sd);
            sql1.append("stamp,");
            tags1.add(jsonObject.getLong("stamp") + "");
        }
        //解析添加数据版本
        if (StringUtils.isNoneBlank(jsonObject.getString("version"))) {
            sql1.append("version,");
            tags1.add(Integer.parseInt(jsonObject.getString("version")));
        }
        //解析添加数据来源（0 网页  1 一体机  2 小程序  3 其他）
        if (StringUtils.isNoneBlank(jsonObject.getInteger("visit_plat") + "")) {
            sql1.append("visit_plat,");
            tags1.add(Integer.parseInt(jsonObject.getInteger("visit_plat") + ""));
        }
        //解析添加访问类型
        if (StringUtils.isNoneBlank(jsonObject.getInteger("visit_type") + "")) {
            sql1.append("visit_type,");
            tags1.add(Integer.parseInt(jsonObject.getInteger("visit_type") + ""));
        }
        //解析添加备注
        if (StringUtils.isNoneBlank(jsonObject.getString("note"))) {
            sql1.append("note,");
            tags1.add(jsonObject.getString("note"));
        }
        //解析添加数据有效性
        if (StringUtils.isNoneBlank(jsonObject.getInteger("is_used") + "")) {
            sql1.append("is_used,");
            tags1.add(Integer.parseInt(jsonObject.getInteger("is_used") + ""));
        }
        sql1.deleteCharAt(sql1.length() - 1);
        sql1.append(") VALUES (");
        for (int i = 1; i <= tags1.size(); i++) {
            sql1.append("?,");
        }
        sql1.deleteCharAt(sql1.length() - 1);
        sql1.append(")");

        //StringBuffer动态写cancel_bespeak_time表sql插入语句
        StringBuffer sql2 = new StringBuffer("INSERT INTO cancel_bespeak_time (");
        List<Object> tags2 = new ArrayList<Object>();
        //添加主键ID
        if (id2 != null && !"".equals(id2)) {
            sql2.append("ID,");
            tags2.add(id2);
        }
        //添加ID，此ID与cancel_bespeak表主键ID一致
        if (id1 != null && !"".equals(id1)) {
            sql2.append("cancel_bes_id,");
            tags2.add(id1);
        }
        //解析添加不接受预约类型（0 1天   1 一周   2 一月）
        if (StringUtils.isNoneBlank(jsonObject.getString("per_long"))) {
            sql2.append("per_long,");
            tags2.add(Integer.parseInt(jsonObject.getString("per_long")));
            //当per_long = 0 时，添加几号
            if ("0".equals(jsonObject.getString("per_long"))) {
                sql2.append("at_date_day,");
                tags2.add(jsonObject.getString("at_date_day"));
            }
            //当per_long = 1 时，添加周几
            if ("1".equals(jsonObject.getString("per_long"))) {
                sql2.append("at_date_week,");
                tags2.add(jsonObject.getString("at_date_week"));
            }
            //当per_long = 2 时，添加几号
            if ("2".equals(jsonObject.getString("per_long"))) {
                sql2.append("at_date_month,");
                tags2.add(jsonObject.getString("at_date_month"));
            }
        }
        //解析添加不接受预约的开始时间
        if (StringUtils.isNoneBlank(jsonObject.getString("start_time"))) {
            sql2.append("start_time,");
            tags2.add(jsonObject.getString("start_time"));
        }
        //解析添加不接受预约的结束时间
        if (StringUtils.isNoneBlank(jsonObject.getString("end_time"))) {
            sql2.append("end_time,");
            tags2.add(jsonObject.getString("end_time"));
        }
        //解析添加时间戳及时间戳转换的创建时间
        if (StringUtils.isNoneBlank(jsonObject.getLong("stamp") + "")) {
            sql2.append("create_time,");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String sd = sdf.format(new Date(Long.parseLong(jsonObject.getLong("stamp") + "")));
            tags2.add(sd);
            sql2.append("stamp,");
            tags2.add(jsonObject.getLong("stamp") + "");
        }
        //解析添加数据版本
        if (StringUtils.isNoneBlank(jsonObject.getString("version"))) {
            sql2.append("version,");
            tags2.add(Integer.parseInt(jsonObject.getString("version")));
        }
        //解析添加数据来源（0 网页  1 一体机  2 小程序  3 其他）
        if (StringUtils.isNoneBlank(jsonObject.getInteger("visit_plat") + "")) {
            sql2.append("visit_plat,");
            tags2.add(Integer.parseInt(jsonObject.getInteger("visit_plat") + ""));
        }
        //解析添加访问类型
        if (StringUtils.isNoneBlank(jsonObject.getInteger("visit_type") + "")) {
            sql2.append("visit_type,");
            tags2.add(Integer.parseInt(jsonObject.getInteger("visit_type") + ""));
        }
        //解析添加备注
        if (StringUtils.isNoneBlank(jsonObject.getString("note"))) {
            sql2.append("note,");
            tags2.add(jsonObject.getString("note"));
        }
        //解析添加数据的有效性
        if (StringUtils.isNoneBlank(jsonObject.getInteger("is_used") + "")) {
            sql2.append("is_used,");
            tags2.add(Integer.parseInt(jsonObject.getInteger("is_used") + ""));
        }
        sql2.deleteCharAt(sql2.length() - 1);
        sql2.append(") VALUES (");
        for (int i = 1; i <= tags2.size(); i++) {
            sql2.append("?,");
        }
        sql2.deleteCharAt(sql2.length() - 1);
        sql2.append(")");


        if ((baseDao.update(sql1.toString(), tags1.toArray()) != 0) && (baseDao.update(sql2.toString(), tags2.toArray()) != 0)) {
            status = Constants.http_status_success;
            info = Constants.http_info_success;
        } else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
        }
        return ResultMap.creatReault(status, info, content);
    }

    //修改
    @Override
    public Object update(String param) {
        String status = null;
        String info = null;
        Object content = null;

        //获取解析JSON字符串
        JSONObject jsonObject = JSON.parseObject(param);

        //StringBuffer动态写sql语句
        StringBuffer sql = new StringBuffer("UPDATE cancel_bespeak cb,cancel_bespeak_time cbt SET");
        List<Object> tags = new ArrayList<Object>();
        //解析修改不接受预约类型（ 0 临时  1 周期）
        if (StringUtils.isNoneBlank(jsonObject.getString("cancel_type"))) {
            sql.append(" cb.cancel_type = ?,");
            tags.add(Integer.parseInt(jsonObject.getString("cancel_type")));
        }
        //解析修改时间戳及时间戳转换的更新时间
        if (StringUtils.isNoneBlank(jsonObject.getLong("stamp") + "")) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String sd = sdf.format(new Date(Long.parseLong(jsonObject.getLong("stamp") + "")));
            sql.append(" cb.update_time = ?,");
            tags.add(sd);
            sql.append(" cb.stamp = ?,");
            tags.add(jsonObject.getLong("stamp") + "");

            sql.append(" cbt.update_time = ?,");
            tags.add(sd);
            sql.append(" cbt.stamp = ?,");
            tags.add(jsonObject.getLong("stamp") + "");
        }
        //解析修改备注
        if (StringUtils.isNoneBlank(jsonObject.getString("note"))) {
            sql.append(" cb.note = ?,");
            tags.add(jsonObject.getString("note"));

            sql.append(" cbt.note = ?,");
            tags.add(jsonObject.getString("note"));
        }
        //解析添加不接受预约类型（0 1天   1 一周   2 一月）
        if (StringUtils.isNoneBlank(jsonObject.getString("per_long"))) {
            sql.append(" cbt.per_long = ?,");
            tags.add(Integer.parseInt(jsonObject.getString("per_long")));

            //当per_long = 0 时，添加几号
            if ("0".equals(jsonObject.getString("per_long"))) {
                sql.append(" cbt.at_date_day = ?,");
                tags.add(jsonObject.getString("at_date_day"));
            }
            //当per_long = 1 时，添加周几
            if ("1".equals(jsonObject.getString("per_long"))) {
                sql.append(" cbt.at_date_week = ?,");
                tags.add(jsonObject.getString("at_date_week"));
            }
            //当per_long = 2 时，添加几号
            if ("2".equals(jsonObject.getString("per_long"))) {
                sql.append(" cbt.at_date_month = ?,");
                tags.add(jsonObject.getString("at_date_month"));
            }
        }
        //解析修改不接受预约开始时间
        if (StringUtils.isNoneBlank(jsonObject.getString("start_time"))) {
            sql.append(" cbt.start_time = ?,");
            tags.add(jsonObject.getString("start_time"));
        }
        //解析修改不接受预约结束时间
        if (StringUtils.isNoneBlank(jsonObject.getString("end_time"))) {
            sql.append(" cbt.end_time = ?,");
            tags.add(jsonObject.getString("end_time"));
        }
        sql.deleteCharAt(sql.length() - 1);

        if (StringUtils.isNoneBlank(jsonObject.getString("id"))) {
            sql.append(" WHERE cb.id = ?");
            tags.add(jsonObject.getString("id"));
            sql.append(" AND cbt.cancel_bes_id = ?");
            tags.add(jsonObject.getString("id"));
        } else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status,info,content);
        }
        if (baseDao.update(sql.toString(), tags.toArray()) != 0){
            status = Constants.http_status_success;
            info = Constants.http_info_success;
        } else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
        }
        return ResultMap.creatReault(status,info,content);
    }

    //查询
    public Object getAllByCondition(String param, HttpServletRequest request) {


        String status = null;
        String info = null;
        Object content = null;

        //获取解析JSON字符串
        JSONObject jsonObject = JSON.parseObject(param);

        StringBuffer sql = new StringBuffer("SELECT DISTINCT\n" +
                "\tb.ID,\n" +
                "\tb.officer_id,\n" +
                "\tb.officer_number,\n" +
                "\tb.officer_name,\n" +
                "\tb.start_time,\n" +
                "\tb.end_time,\n" +
                "\tb.per_long,\n" +
                "\tb.at_date_day,\n" +
                "\tb.at_date_week,\n" +
                "\tb.at_date_month,\n" +
                "\n" +
                "IF (\n" +
                "\t(\n" +
                "\t\t? NOT IN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\toffr.officer_id\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tofficer_role offr\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\toffr.role_id IN ('leader18f705d08db6479d8e14b8d11b')\n" +
                "\t\t)\n" +
                "\t),\n" +
                "\n" +
                "IF (\n" +
                "\t(\n" +
                "\t\t? IN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tod.officer_id\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tofficer_department od\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\tod.officer_id NOT IN (\n" +
                "\t\t\t\t\tSELECT DISTINCT\n" +
                "\t\t\t\t\t\todd.parent_id\n" +
                "\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\tofficer_department odd\n" +
                "\t\t\t\t)\n" +
                "\t\t)\n" +
                "\t),\n" +
                "\n" +
                "IF ((b.officer_id = ?), 1, 0),\n" +
                "\n" +
                "IF ((b.officer_id = ?), 1, 0)\n" +
                "),\n" +
                " 0\n" +
                ") AS flag\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tcb.ID,\n" +
                "\t\t\tcb.officer_id,\n" +
                "\t\t\to.officer_number,\n" +
                "\t\t\to.officer_name,\n" +
                "\t\t\td.dep_name,\n" +
                "\t\t\tcbt.start_time,\n" +
                "\t\t\tcbt.end_time,\n" +
                "\t\t\tcbt.per_long,\n" +
                "\t\t\tcbt.at_date_day,\n" +
                "\t\t\tcbt.at_date_week,\n" +
                "\t\t\tcbt.at_date_month\n" +
                "\t\tFROM\n" +
                "\t\t\tofficer o,\n" +
                "\t\t\tofficer_department od,\n" +
                "\t\t\tofficer_role off_rol,\n" +
                "\t\t\tcancel_bespeak cb,\n" +
                "\t\t\tcancel_bespeak_time cbt,\n" +
                "\t\t\tdepartments d,\n" +
                "\t\t\trole r\n" +
                "\t\tWHERE\n" +
                "\t\t\tcb.ID = cbt.cancel_bes_id\n" +
                "\t\tAND o.ID = cb.officer_id\n" +
                "\t\tAND od.officer_id = cb.officer_id\n" +
                "\t\tAND od.dep_id = d.ID\n" +
                "\t\tAND cb.officer_id = off_rol.officer_id\n" +
                "\t\tAND off_rol.role_id = r.ID\n" +
                "\t\tAND cb.version = ?\n" +
                "\t\tAND cbt.version = ?\n" +
                "\t\tAND cb.visit_type = ?\n" +
                "\t\tAND cbt.visit_type = ?\n" +
                "\t\tAND cb.is_used = ?\n" +
                "\t\tAND cbt.is_used = ?\n" +
                "\t\tAND\n" +
                "\t\tIF (\n" +
                "\t\t\t? NOT IN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\toffr.officer_id\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tofficer_role offr\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\toffr.role_id IN ('leader18f705d08db6479d8e14b8d11b')\n" +
                "\t\t\t),\n" +
                "\t\t\t(\n" +
                "\n" +
                "\t\t\t\tIF (\n" +
                "\t\t\t\t\t(\n" +
                "\t\t\t\t\t\t? IN (\n" +
                "\t\t\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\t\t\tod.officer_id\n" +
                "\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\tofficer_department od\n" +
                "\t\t\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\t\t\tod.officer_id NOT IN (\n" +
                "\t\t\t\t\t\t\t\t\tSELECT DISTINCT\n" +
                "\t\t\t\t\t\t\t\t\t\todd.parent_id\n" +
                "\t\t\t\t\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t\t\t\t\tofficer_department odd\n" +
                "\t\t\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t\t)\n" +
                "\t\t\t\t\t),\n" +
                "\t\t\t\t\t(od.officer_id = ?),\n" +
                "\t\t\t\t\t(od.parent_id = ?)\n" +
                "\t\t\t\t)\n" +
                "\t\t\t),\n" +
                "\t\t\t(1 = 1)\n" +
                "\t\t)\n" +
                "\t) b");
        List<Object> tags = new ArrayList<Object>();
        //解析办事员ID，如为空则返回参数为空
        if (StringUtils.isNoneBlank(jsonObject.getString("officer_id"))){
            Object officerId = jsonObject.getString("officer_id");
            for (int i = 0; i < 4; i++){
                tags.add(officerId);
            }
        } else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status,info,content);
        }
        //解析查询数据版本
        if (StringUtils.isNoneBlank(jsonObject.getString("version"))){
            tags.add(Integer.parseInt(jsonObject.getString("version")));
            tags.add(Integer.parseInt(jsonObject.getString("version")));
        }
        //解析查询访问类型
        if (StringUtils.isNoneBlank(jsonObject.getString("visit_type"))){
            tags.add(Integer.parseInt(jsonObject.getString("visit_type")));
            tags.add(Integer.parseInt(jsonObject.getString("visit_type")));
        }
        //解析查询数据有效性
        if (StringUtils.isNoneBlank(jsonObject.getString("is_used"))){
            tags.add(Integer.parseInt(jsonObject.getString("is_used")));
            tags.add(Integer.parseInt(jsonObject.getString("is_used")));
        }
        //解析办事员ID，如为空则返回参数为空
        if (StringUtils.isNoneBlank(jsonObject.getString("officer_id"))){
            Object officerId = jsonObject.getString("officer_id");
            for (int i = 0; i < 4; i++){
                tags.add(officerId);
            }
        } else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status,info,content);
        }
        //条件搜索，员工姓名
        if (StringUtils.isNoneBlank(jsonObject.getString("officer_name"))){
            sql.append(" WHERE officer_name LIKE ?");
            tags.add("%" + jsonObject.getString("officer_name") + "%");
        }
        sql.append(" ORDER BY b.officer_name");
        //分页
        Integer page = Integer.valueOf(request.getParameter("offset"));
        Integer rows = Integer.valueOf(request.getParameter("limit"));

        Long total = null;
        String countsql = "select count(1) as count from (" + sql.toString() + ") as t";
        try {
            total = baseDao.getCount(countsql, tags);
        } catch (Exception e) {
            logger.error(e.getMessage());
        }


        String sql1 = sql.toString() + " limit " + page + "," + rows + "";

        try {
            content = baseDao.queryForList(sql1, tags.toArray());
            status = Constants.http_status_success;
            info = Constants.http_info_success;
        } catch (Exception e) {
            logger.error(e.getMessage());
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "后台处理异常";
        }

        LinkedHashMap<Object, Object> map = ResultMap.creatReault(status, info, content);
        map.put("total", total);
        return map;
    }


    //办事员不接受预约时间记录删除
    public Object dpmtReservationRemove(String param, HttpServletRequest request) {

        String status = null;
        String info = null;
        Object content = null;

        //获取解析JSON字符串
        JSONObject jsonObject = JSON.parseObject(param);

        //**********************************************************
        if (null == jsonObject) {
            return ResultMap.creatReault(Constants.http_status_fail, Constants.http_info_fali, "参数为空");
        }

        LinkedHashMap<Object, Object> map = null;

        String systoken = null;
        if (!(jsonObject.getInteger("visit_plat") != 0)) { // 网页端
            systoken = jsonObject.getString("systoken"); // 全局token
            if (StringUtils.isBlank(systoken)) {
                logger.info("systoken为空");
                map = ResultMap.creatReault("33", "失败", "token为空");
                logger.info("出去《办事员不接受预约时间记录删除》dpmtReservationRemove()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }
        }

        String dwellerIdcard = null;
        String dwellerId = null;
        if (jsonObject.getInteger("visit_plat") == 0) { // 网页端

            // 根据身份证从redis中获取token
            Jedis jedis = new Jedis("140.143.210.101", 6379);
            jedis.auth("q1w2e3r4t5");

            List<String> infoList = jedis.hmget(systoken, "systoken",  "dwellerIdcard", "dwellerId");
            String systokenRedis = null;
            if (infoList != null && infoList.size() > 0) {
                systokenRedis = infoList.get(0); // token
                dwellerIdcard = infoList.get(1); // 居民身份证号
                dwellerId = infoList.get(2); // 居民唯一标识
            }

            // 查看token是否过期
            if (StringUtils.isBlank(systokenRedis)) { // 重新登录，弹出登录框
                logger.info("全局systoken过期，需要重新获取systoken");
                map = ResultMap.creatReault("33", "失败", "systoken过期");
                logger.info("出去《办事员不接受预约时间记录删除》dpmtReservationRemove()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

            // token不一致
            if (!StringUtils.equals(systokenRedis, systoken)) {
                logger.info("用户访问的token不一致");
                map = ResultMap.creatReault("331", "失败", "systoken不一致");
                logger.info("出去《办事员不接受预约时间记录删除》dpmtReservationRemove()方法；出参 = " + JSON.toJSONString(map));
                return map;
            }

        }
        //**********************************************************

        //StringBuffer动态写sql语句
        StringBuffer sql = new StringBuffer("UPDATE cancel_bespeak cb,cancel_bespeak_time cbt SET");
        List<Object> tags = new ArrayList<Object>();
        //解析修改数据的有效性
        if (StringUtils.isNoneBlank(jsonObject.getString("is_used"))){
            sql.append(" cbt.is_used = ?");
            tags.add(jsonObject.getString("is_used"));

            sql.append(" ,cb.is_used = ?");
            tags.add(jsonObject.getString("is_used"));
        }
        //解析修改时间戳及时间戳转换的更新时间
        if (StringUtils.isNoneBlank(jsonObject.getLong("stamp") + "")) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String sd = sdf.format(new Date(Long.parseLong(jsonObject.getLong("stamp") + "")));
            sql.append(" ,cb.update_time = ?");
            tags.add(sd);
            sql.append(" ,cb.stamp = ?");
            tags.add(jsonObject.getLong("stamp") + "");

            sql.append(" ,cbt.update_time = ?");
            tags.add(sd);
            sql.append(" ,cbt.stamp = ?");
            tags.add(jsonObject.getLong("stamp") + "");
        }
        if (StringUtils.isNoneBlank(jsonObject.getString("id"))) {
            sql.append(" WHERE cb.id = ?");
            tags.add(jsonObject.getString("id"));
            sql.append(" AND cbt.cancel_bes_id = ?");
            tags.add(jsonObject.getString("id"));
        } else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
            content = "参数为空";
            return ResultMap.creatReault(status,info,content);
        }
        if (baseDao.update(sql.toString(), tags.toArray()) != 0){
            status = Constants.http_status_success;
            info = Constants.http_info_success;
        } else {
            status = Constants.http_status_fail;
            info = Constants.http_info_fali;
        }
        return ResultMap.creatReault(status,info,content);
    }
}